package com.speed.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.aop.Before;
import com.jfinal.core.Controller;
import com.jfinal.ext.interceptor.POST;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.speed.entity.SeatInfo;
import com.speed.service.ActiviterService;
import com.speed.service.ActivitesService;
import com.speed.service.SeatService;
import com.speed.util.ecxel.ExcelResolver;
import com.speed.util.ecxel.ImportDataFactory;
import com.speed.util.ecxel.ImportTempData;
import com.speed.util.ecxel.TempData;
import com.speed.vo.SeatInfoVO;
import com.speed.entity.Activiter;
import com.speed.entity.Activtites;
import com.speed.entity.PersonInfoToView;
import com.speed.entity.Seat;

import com.speed.util.StringUtils;
import com.speed.util.UuidGenerator;
import com.speed.util.ecxel.ExcelResolver;
import com.speed.util.ecxel.ImportTempData;
import com.speed.util.ecxel.TempData;

public class SeatController extends Controller {

	public static int seat_id;

	public static int activiter_id;

	public static String seat_no;

	public static String getSeat_no() {
		return seat_no;
	}

	public static void setSeat_no(String seat_no) {
		SeatController.seat_no = seat_no;
	}

	public int getActiviter_id() {
		return activiter_id;
	}

	public void setActiviter_id(int activiter_id) {
		this.activiter_id = activiter_id;
	}

	public int getSeat_id() {
		return seat_id;
	}

	public void setSeat_id(int seat_id) {
		this.seat_id = seat_id;
	}

	public void index() {

		List<SeatInfo> list = SeatInfo.MODEL_DAO.find("select * from " + SeatInfo.TABLE_NAME);

		setAttr("seatlist", list);
		render("index.jsp");

	}

	public void print() {

	}

	public void seat() {
		renderJsp("seat.jsp");
	}

	public void setPeople() {

		String activiter_name = getPara("activiter_name");
		String activiter_mobile = getPara("activiter_mobile");
		String sign_code = getPara("sign_code");

		// this.setSeat_id(st_id);
		String SQL = "SELECT * FROM s_activiter WHERE  " + "activiter_name=  " + "'" + activiter_name + "'" + "  AND  "
				+ "  activiter_mobile =   " + "'" + activiter_mobile + "'" + "  AND " + "sign_code = " + "'" + sign_code
				+ "'";
		Activiter activiter = Activiter.MODEL_DAO.findFirst(SQL);

		long activiter_id = activiter.get(activiter.ID);
		String st_no = this.getSeat_no();

		boolean b = new SeatInfo().set("seat_no", "" + seat_no).set("activiter_id", activiter_id).update();
		System.out.println(b);
		renderText("选定座位成功！");

	}

	/**
	 * 清空指定ID的座位人員
	 */
	public void clearseat() {
		String seatID = getPara("seatID");

		SeatInfo seatInfo = SeatInfo.MODEL_DAO
				.findFirst("select * from " + SeatInfo.TABLE_NAME + " where " + SeatInfo.ID + " = ? ", seatID);
		boolean update = new SeatInfo().put(seatInfo).set(SeatInfo.ACTIVITER_ID, null).update();
		System.out.println(update);
		JSONObject json = new JSONObject();
		json.put("msg", "清空成功");
		renderJson(json.toJSONString());
	}

	public void bind() {
		String seatID = getPara("seatID");
		// String seatNo = getPara("seatNo");
		String mobile = getPara("mobile");
		String signCode = getPara("signCode");

		ActiviterService activiterService = enhance(ActiviterService.class);
		Map<String, Object> params = new HashMap<String, Object>();
		params.put(Activiter.ACTIVITER_MOBILE, mobile);
		params.put(Activiter.SIGN_CODE, signCode);
		// 查詢到的活動者
		Activiter activiter = activiterService.getActiviterByCoditionForOne(params);

		SeatInfo seatInfo = SeatInfo.MODEL_DAO
				.findFirst("select * from " + SeatInfo.TABLE_NAME + " where " + SeatInfo.ID + " = ? ", seatID);
		boolean update = new SeatInfo().put(seatInfo).set(SeatInfo.ACTIVITER_ID, activiter.get(Activiter.ID)).update();
		System.out.println(update);
		JSONObject json = new JSONObject();
		json.put("msg", "調用成功");
		renderJson(json.toJSONString());

	}

	public void search() {
		JSONObject j = new JSONObject();
		int pageSize = 5;
		String signCode = getPara("nameOrMobile");// 电话号码
		String pageNumber = getPara("pageNumber");
		int currentpage = 1;
		if (pageNumber != null) {
			currentpage = Integer.parseInt(pageNumber);
		}
		Activtites activtites = (Activtites) getSession().getAttribute("activtites");
		SeatService seatService = enhance(SeatService.class);
		// 已经被占用的座位
		List<SeatInfo> seatInfos = seatService.findActivitersHasSeat(activtites.getInt(Activtites.ID));
		List<Long> activiterIDs = new ArrayList<Long>();
		if (!seatInfos.isEmpty()) {
			for (SeatInfo s : seatInfos) {
				activiterIDs.add(s.getLong(SeatInfo.ACTIVITER_ID));
			}
		}
		ActiviterService activiterService = enhance(ActiviterService.class);
		Page<Activiter> page = activiterService.findActivitersHasNoSeatByPage(currentpage, pageSize, activiterIDs,
				activtites.getInt(Activtites.ID), signCode);
		if (page != null) {
			j.put("message", "success");
			j.put("list", page.getList());
			j.put("currentpage", page.getPageNumber());
			j.put("totalpage", page.getTotalPage());
		} else {
			j.put("message", "fail");
		}
		renderJson(j);
	}

	public static List<TempData> readFromXLSX2003(String path) throws IOException {

		List<ImportTempData> itempDatas = new ArrayList<ImportTempData>();

		ImportTempData tempData0 = new ImportTempData() {
			@Override
			public void inputData(Row row, TempData tempData) {
				Cell cell = row.getCell(0);
				int cellType = cell.getCellType();
				if (Cell.CELL_TYPE_STRING == cellType) {
					String mobile = cell.getStringCellValue();
					tempData.setData("no", StringUtils.replaceBlank(mobile));
				} else if (Cell.CELL_TYPE_NUMERIC == cellType) {
					Double mobile = cell.getNumericCellValue();
					tempData.setData("no", BigDecimal.valueOf(mobile).toPlainString());
				}
			}
		};

		ImportTempData tempData1 = new ImportTempData() {
			@Override
			public void inputData(Row row, TempData tempData) {
				Cell cell = row.getCell(1);
				int cellType = cell.getCellType();
				if (Cell.CELL_TYPE_STRING == cellType) {
					String mobile = cell.getStringCellValue();
					tempData.setData("SeatNo", StringUtils.replaceBlank(mobile));
				} else if (Cell.CELL_TYPE_NUMERIC == cellType) {
					Double mobile = cell.getNumericCellValue();
					tempData.setData("SeatNo", BigDecimal.valueOf(mobile).toPlainString());
				}
			}
		};

		itempDatas.add(tempData0);
		itempDatas.add(tempData1);
		List<TempData> tempDatas = new ArrayList<TempData>();
		try {
			File file = new File(path);
			FileInputStream fis = new FileInputStream(file);
			tempDatas = ExcelResolver.importExcelData(fis, true, itempDatas);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return tempDatas;

	}

	/**
	 * 初始化展示所有座位
	 */
	public void show() {
		try {
			// 1.这里先获取活动内容 -- 从ID获取活动表数据
			Integer activtitesID = getParaToInt(0);
			String content = getPara(1);
			ActivitesService activitesService = enhance(ActivitesService.class);
			// 刷新activtites
			Activtites activtites = activitesService.findActivtitesById(activtitesID);
			setSessionAttr("activtites", activtites);

			// 2.默认分页 页码起始1 页容16
			Integer pageNumber = 1;
			Integer pageSize = 16;

			StringBuffer sql = new StringBuffer("from " + SeatInfo.TABLE_NAME + " s  LEFT JOIN " + Activiter.TABLE_NAME
					+ " a ON s." + SeatInfo.ACTIVITER_ID + " = a." + Activiter.ID + " where " + " s."
					+ SeatInfo.ACTIVITES_ID + " = ? ");
			if (StrKit.notBlank(content)) {
				sql.append(" AND " + SeatInfo.SEAT_NO + " like '%" + content + "%'");
			}

			Page<Record> result = Db.paginate(pageNumber, pageSize,
					"select " + "s." + SeatInfo.SEAT_NO + " seatNo ," + "s." + SeatInfo.ID + " seatId" + ", a."
							+ Activiter.ACTIVITER_NAME + " name , a." + Activiter.ACTIVITER_MOBILE + " mobile",
					sql.toString(), activtitesID);

			List<SeatInfoVO> vos = makeSeatInfoVO(result);
			setAttr("seatInfoVos", vos);
			setAttr("totalPages", result.getTotalPage());
			render("seat.jsp");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 制造展示数据
	 * 
	 * @param record
	 * @return
	 */
	private List<SeatInfoVO> makeSeatInfoVO(Page<Record> record) {
		List<Record> datas = record.getList();
		List<SeatInfoVO> vos = new ArrayList<SeatInfoVO>();
		for (Record r : datas) {
			SeatInfoVO vo = new SeatInfoVO();
			vo.setSeatId(r.getStr("seatId") == null ? "" : r.getStr("seatId"));
			vo.setSeatNo(r.getStr("seatNo") == null ? "" : r.getStr("seatNo"));
			vo.setMobile(r.getStr("mobile") == null ? "" : r.getStr("mobile"));
			vo.setName(r.getStr("name") == null ? "" : r.getStr("name"));
			vos.add(vo);
		}
		return vos;
	}

	@Before(POST.class)
	public void list() {
		Activtites activtites = getSessionAttr("activtites");
		Integer pageNumber = getParaToInt("page");
		// Integer pageSize = getParaToInt("pageSize");
		Integer pageSize = 16;
		Page<Record> result = Db.paginate(pageNumber, pageSize,
				"select " + "s." + SeatInfo.SEAT_NO + " seatNo ," + "s." + SeatInfo.ID + " seatId" + ", a."
						+ Activiter.ACTIVITER_NAME + " name , a." + Activiter.ACTIVITER_MOBILE + " mobile",
				"from " + SeatInfo.TABLE_NAME + " s  LEFT JOIN " + Activiter.TABLE_NAME + " a ON s."
						+ SeatInfo.ACTIVITER_ID + " = a." + Activiter.ID + " where " + " s." + SeatInfo.ACTIVITES_ID
						+ " = ? ",
				activtites.get(Activtites.ID));
		List<SeatInfoVO> vos = makeSeatInfoVO(result);
		JSONObject json = new JSONObject();
		json.put("seatInfoVos", vos);
		json.put("totalPages", result.getTotalPage());
		json.put("currPage", pageNumber);
		renderJson(json.toJSONString());
	}

	/**
	 * 导入excel座位数据
	 */
	private void importSeatInfos(List<TempData> tempDatas, int activitesId) {
		for (TempData t : tempDatas) {
			Map<String, Object> temp = t.getData();
			String seatNo = (String) temp.get("seatNo");

			// 保存座位信息

			String SQL = "SELECT * FROM   " + SeatInfo.TABLE_NAME + "  WHERE seat_no = " + "'" + seatNo + "'"
					+ " AND activites_id =" + "'" + activitesId + "'";

			List<SeatInfo> list = new SeatInfo().find(SQL);

			if (list.isEmpty()) {

				new SeatInfo().set(SeatInfo.ID, UuidGenerator.gener()).set(SeatInfo.ACTIVITES_ID, activitesId)
						.set(SeatInfo.SEAT_NO, seatNo).save();
			} else {
				new SeatInfo().set(SeatInfo.ID, UuidGenerator.gener()).set(SeatInfo.ACTIVITES_ID, activitesId)
						.set(SeatInfo.SEAT_NO, seatNo).update();
			}
		}
	}

	public boolean isMobileCode(String isMobileCode) {
		String value = isMobileCode;

		String regExp = "^[1]([3][0-9]{1}|59|58|88|89)[0-9]{8}$";

		Pattern p = Pattern.compile(regExp);

		Matcher m = p.matcher(value);

		return m.find();// boolean
	}

	@Before(POST.class)
	public void importseat() throws IOException {

		List<TempData> tempDatas;
		UploadFile file = getFile("excelFile");
		File source = file.getFile();
		Activtites activtites = (Activtites) getSession().getAttribute("activtites");
		// FileInputStream fis = new FileInputStream(source);
		String path = source.getAbsolutePath();

		String activtites_id = activtites.get(Activtites.ID).toString();
		tempDatas = readFromXLSAutoSeat(path);

		for (TempData t : tempDatas) {
			Map<String, Object> temp = t.getData();
			String seatNo = (String) temp.get("SeatNo");
			String ActiviterName = (String) temp.get("ActiviterName");
			String mobile = (String) temp.get("mobile");
			// 保存座位信息
			String SQL = "select t.id from tp_activiter  t WHERE t.activiter_name = ? and t.activiter_mobile=? ";
			Record r = Db.findFirst(SQL, ActiviterName, mobile);
			if (r != null) {
				String activiter_id = r.getNumber("id") + "";
				try {

					List<Record> listseat = Db
							.find("SELECT * FROM " + SeatInfo.TABLE_NAME + " WHERE seat_no=" + "'" + seatNo + "'");
					SeatInfo tempSeatInfo = new SeatInfo();
					if (listseat.isEmpty()) {

						tempSeatInfo.set("seat_no", seatNo).set("activiter_id", new BigInteger(activiter_id))
								.set("activites_id", activtites_id).set("id", UuidGenerator.gener()).save();
					} else {

						String id = listseat.get(0).get(SeatInfo.ID).toString();
						tempSeatInfo.set("seat_no", seatNo).set("activiter_id", activiter_id)
								.set("activites_id", activtites_id).set("id", id).update();
					}

				} catch (Exception e) {
					e.printStackTrace();
				}

			}

		}

		renderText("导入成功！");
	}

	public void uploadexcel() {
		renderJsp("upload_dispose.jsp");
	}

	public static List<TempData> readFromXLSAutoSeat(String path) throws IOException {

		List<ImportTempData> itempDatas = new ArrayList<ImportTempData>();

		ImportDataFactory factory = ImportDataFactory.INSTANCE;
		itempDatas.add(factory.createImportTempData("SeatNo", 0));
		itempDatas.add(factory.createImportTempData("ActiviterName", 1));
		itempDatas.add(factory.createImportTempData("mobile", 2));

		List<TempData> tempDatas = new ArrayList<TempData>();
		try {
			File file = new File(path);
			FileInputStream fis = new FileInputStream(file);
			tempDatas = ExcelResolver.importExcelData(fis, true, itempDatas);

			for (TempData t : tempDatas) {
				Object object0 = t.getData().get("SeatNo");
				System.out.print(object0);

				Object object1 = t.getData().get("ActiviterName");
				System.out.print(object1);

				Object object2 = t.getData().get("mobile");
				System.out.println(object2);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return tempDatas;

	}

	public void readexcel() {
		renderJsp("uploadexcel.jsp");
	}

	@Before(POST.class)
	public void viewSeat2() {

		List<TempData> tempDatas;
		UploadFile file = getFile("excelFile");
		File source = file.getFile();

		// FileInputStream fis = new FileInputStream(source);
		String path = source.getAbsolutePath();

		// 1.这里先获取活动内容 -- 从ID获取活动表数据
		Integer activites_id = getParaToInt(0);
		// activites_id = 1; //暂时定为 活动0
		Activtites activtites = Activtites.MODEL_DAO.findFirst(
				"select " + Activtites.ID + " from " + Activtites.TABLE_NAME + " where " + Activtites.ID + " = ? ",
				activites_id);
		if (activtites == null) {
			// 没有活动，不要导入数据，返回前端提示一下
			// 代码区
			return;
		}
		// 导入Excel数据
		try {
			importSeatInfos(readFromXLSX2003(path), activites_id);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// 查询表格并显示

		String SQL = "SELECT  *  FROM  " + SeatInfo.TABLE_NAME + " WHERE activites_id= " + activites_id;
		List<SeatInfo> list = new SeatInfo().MODEL_DAO.find(SQL);
		PersonInfoToView personInfoToView = new PersonInfoToView();

		List<PersonInfoToView> personInfoToViews = new ArrayList<PersonInfoToView>();

		List<Record> personInfoToViewRecord = Db.find(
				"SELECT seat_no, seat.id seatID,act.* FROM   tp_seatinfo seat left join  tp_activiter act on seat.activiter_id = act.id ");

		String SQL_ACTIVITER = "SELECT  *  FROM  " + Activiter.TABLE_NAME;

		List<Activiter> act_list = Activiter.MODEL_DAO.find(SQL_ACTIVITER);
		String activites_name = Activtites.MODEL_DAO.findById("1").getStr("activites_name");

		setAttr("act_list", act_list);
		setAttr("seatinfo_list", list);
		setAttr("activites_name", activites_name);
		setAttr("personInfoList", personInfoToViewRecord);
		render("seat.jsp");

	}

	@Before(POST.class)
	public void importbyhand() throws IOException {

		UploadFile file = getFile("excelFile");
		File source = file.getFile();
		Activtites activtites = (Activtites) getSession().getAttribute("activtites");

		// FileInputStream fis = new FileInputStream(source);
		String path = source.getAbsolutePath();

		String activtites_id = activtites.get(Activtites.ID).toString();

		List<TempData> tempDatas = readFromXLSX2003(path);

		for (TempData t : tempDatas) {
			Map<String, Object> temp = t.getData();
			String seatNo = (String) temp.get("SeatNo");

			String SQL = "select t.id from tp_seatinfo  t WHERE t.seat_no = ?";
			Record r = Db.findFirst(SQL, seatNo);
			if (r == null) {

				SeatInfo tempSeatInfo = new SeatInfo();
				tempSeatInfo.set("seat_no", seatNo).set("activites_id", activtites_id).set("id", UuidGenerator.gener())
						.save();

			}

			else {

				String id = r.get(SeatInfo.ID).toString();
				SeatInfo tempSeatInfo = new SeatInfo();
				tempSeatInfo.set("seat_no", seatNo).set("activites_id", activtites_id).set("id", id).update();
			}
		}

		renderText("导入成功！");
	}

	public void uploadbyhand() {
		renderJsp("uploadbyhand.jsp");
	}

}